package com.gzy.studentsociety.dao;

import com.gzy.studentsociety.entity.Member;
import com.gzy.studentsociety.entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

import static com.sun.xml.internal.ws.encoding.xml.XMLMessage.getDataSource;

public class MemberDaoImpl extends BaseDao implements MemberDao{
    @Override
    public Integer addMember(Member member) throws SQLException {
        String sql = "insert into member (memberStudentId,joinDate,member_societyId) value (?,?,?)";
            QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,member.getMemberStudentId(),member.getJoinDate(),member.getMember_associationId());
    }
    @Override
    public List<Member> queryAllMember(Integer status,Integer socityId) throws SQLException {
        String sql = "select * from member where memberStatus = ? and member_societyId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql, new BeanListHandler<Member>(Member.class),status,socityId);
    }

//    @Override
//    public List<Student> queryStudent(String name, String number) throws SQLException {
////        return null;
//        QueryRunner qr = new QueryRunner(getDataSource());
//        String sql = "select * from student where studentName = ? and studentNumber = ?";
//        return qr.query(sql, new BeanListHandler<Student>(Student.class), name, number);
//    }

    @Override
    public Integer isMemberIn(Member member) throws SQLException {
        String sql = "select count(1) from member where member_societyId = ? and memberStudentId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql, new ScalarHandler<Long>(),member.getMember_associationId(), member.getMemberStudentId()).intValue();
    }
    @Override
    public Integer deleteGraduated(Integer assId) throws SQLException {
        String sql = "update member,student set member.memberStatus = 2 where member.member_societyId = ? and member.memberStudentId = student.studentId and student.graduated = 2";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,assId);
    }
    @Override
    public Integer deleteMember(Integer id, Integer status) throws SQLException {
        String sql = "update member set member.memberStatus = ? where memberId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,status,id);
    }
    @Override
    //假设学生只参加一个协会
    public Member queryByNumber(String number) throws SQLException {
        String sql = "select m.* from member m,student s where s.studentNumber = ? and m.memberStudentId = s.studentId";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql,new BeanHandler<Member>(Member.class),number);
    }
}
